package lectores.parsersXLSX;

import java.sql.SQLException;
import lectores.LectorConvenios;
import org.xml.sax.helpers.DefaultHandler;
import lectores.ExcelMalFormadoException;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;

public class XLSXHandler extends DefaultHandler {
    
    enum xssfDataType {
        BOOL,
        ERROR,
        FORMULA,
        INLINESTR,
        SSTINDEX,
        NUMBER,
    }
    
    StylesTable stylesTable;
    ReadOnlySharedStringsTable sharedStringsTable;
    boolean vIsOpen = false;
    int valoresLeidos = 0;
    xssfDataType nextDataType;
    short formatIndex;
    String formatString;
    int thisColumn = -1;
    int lastColumnNumber = -1;
    StringBuffer value;
    String sqlValues = "";
    LectorConvenios lector;
    int mesALeer;
    int renglonTratado = -1;
    int conteoCeldas = -1;
    int tipoFiltroPeriodo;
    
    public XLSXHandler(StylesTable styles, ReadOnlySharedStringsTable strings, LectorConvenios lector, int mesALeer, int tipoFiltroPeriodo) {
        this.stylesTable = styles;
        this.sharedStringsTable = strings;
        this.value = new StringBuffer();
        this.nextDataType = xssfDataType.NUMBER;
        this.lector = lector;
        this.mesALeer = mesALeer;
        this.tipoFiltroPeriodo = tipoFiltroPeriodo;
    }
    
    public void startElement(String uri, String localName, String name, Attributes attributes)
                throws SAXException {
        if ("inlineStr".equals(name) || "v".equals(name)) {
            vIsOpen = true;
            valoresLeidos++;
            value.setLength(0);
        }
        else if ("c".equals(name)) {
            valoresLeidos = 0;
                
            String r = attributes.getValue("r");
            int firstDigit = -1;
            for (int c = 0; c < r.length(); ++c) {
                if (Character.isDigit(r.charAt(c))) {
                    firstDigit = c;
                    break;
                }
            }
                
            thisColumn = nameToColumn(r.substring(0, firstDigit));
                
            this.nextDataType = xssfDataType.NUMBER;
            this.formatIndex = -1;
            this.formatString = null;
            String cellType = attributes.getValue("t");
            String cellStyleStr = attributes.getValue("s");
            if ("b".equals(cellType))
                nextDataType = xssfDataType.BOOL;
            else if ("e".equals(cellType))
                nextDataType = xssfDataType.ERROR;
            else if ("inlineStr".equals(cellType))
                nextDataType = xssfDataType.INLINESTR;
            else if ("s".equals(cellType))
                nextDataType = xssfDataType.SSTINDEX;
            else if ("str".equals(cellType))
                nextDataType = xssfDataType.FORMULA;
            else if (cellStyleStr != null) {
                int styleIndex = Integer.parseInt(cellStyleStr);
                XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
                this.formatIndex = style.getDataFormat();
                this.formatString = style.getDataFormatString();
                if (this.formatString == null)
                    this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
            }
        }
    }

    public void endElement(String uri, String localName, String name)
            throws SAXException {
        String thisStr = "";
        
        if("c".equals(name)) {
            conteoCeldas++;

            if(conteoCeldas < thisColumn) {
                int diferencia = thisColumn - conteoCeldas;
                if(valoresLeidos != 0) {
                    String[] actualSQLValues = sqlValues.split("\\|");
                    String ultimoValorLeido = actualSQLValues[actualSQLValues.length - 1];
                    sqlValues = "";
                    for(int i = 0; i < actualSQLValues.length - 1; i++) {
                        sqlValues = sqlValues + actualSQLValues[i] + "|";
                    }
                    if((conteoCeldas == 2 && thisColumn == 3) || (conteoCeldas == 12 && thisColumn == 13)) {
                        sqlValues = sqlValues + "-1|";
                    }
                    for(int i = 0; i < diferencia; i++) {
                        sqlValues = sqlValues + "0|";
                    }
                    sqlValues = sqlValues + ultimoValorLeido + "|";
                } else {
                    if((conteoCeldas == 2 && thisColumn == 3) || (conteoCeldas == 12 && thisColumn == 13)) {
                        sqlValues = sqlValues + "-1|";
                    }
                    for(int i = 0; i < diferencia; i++) {
                        sqlValues = sqlValues + "0|";
                    }
                }
                conteoCeldas = thisColumn;
            }
                
            if(valoresLeidos == 0) {
                if(thisColumn == 2 || thisColumn == 12) {
                  sqlValues = sqlValues + "-1|";  
                } else {
                  sqlValues = sqlValues + "0|";
                }
            }
                
        } else if ("v".equals(name)) {
            vIsOpen = false;
                
            switch (nextDataType) {
                
                case BOOL:
                    char first = value.charAt(0);
                    thisStr = first == '0' ? "FALSE" : "TRUE";
                    break;

                case ERROR:
                    thisStr = "\"ERROR:" + value.toString() + '"';
                    break;
                    
                case FORMULA:
                    thisStr = '"' + value.toString() + '"';
                    break;
                    
                case INLINESTR:
                    XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
                    thisStr = "'" + rtsi.toString() + "'";
                    break;
                    
                case SSTINDEX:
                    String sstIndex = value.toString();
                    try {
                        int idx = Integer.parseInt(sstIndex);
                        XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
                        thisStr = "'" + rtss.toString() +  "'";
                    }
                    catch (NumberFormatException ex) {
                        String cad = "Failed to parse SST index '" + sstIndex + "': " + ex.toString(); 
                        System.exit(0);
                    }
                    break;
                    
                case NUMBER:
                    thisStr = value.toString();
                    break;
                    
                default:
                    thisStr = "(TODO: Unexpected type: " + nextDataType + ")";
                    break;
            }
            
            if (lastColumnNumber == -1) {
                lastColumnNumber = 0;
            }
            
            sqlValues = sqlValues + thisStr + "|";
            
            if (thisColumn > -1)
                lastColumnNumber = thisColumn;
            
        } else if ("row".equals(name)) {
            conteoCeldas = -1;
            renglonTratado++;
            
            int mes = 0;
            if(renglonTratado == 0) {
                sqlValues = "";
                lastColumnNumber = -1;
                return; 
            } else {
                mes = validaMes(sqlValues.split("\\|")[0]);
            }
            if(mes == -1) {
                String tmpVal = "P_INV|";
                String[] splitVals = sqlValues.split("\\|");
                for(int j = 1; j < splitVals.length; j++) {
                    tmpVal = tmpVal + splitVals[j] + "|";
                }
                sqlValues = tmpVal;
                
            } else {
                switch(tipoFiltroPeriodo) {
                    case LectorConvenios.FILTRO_BONOS_PRODUCCION:
                        if(mesALeer % 3 == 0) {
                            if(!(mesALeer - 2 <= mes && mes <= mesALeer)) {
                                sqlValues = "";
                                lastColumnNumber = -1;
                                return; 
                            }
                        } else {
                            if(mes != mesALeer) {
                                sqlValues = "";
                                lastColumnNumber = -1;
                                return;
                            } 
                        }
                        break;
                    case LectorConvenios.FILTRO_BONOS_CALIDAD:
                        if(mesALeer < mes) {
                            sqlValues = "";
                            lastColumnNumber = -1;
                            return;
                        }
                        break;
                }
            }
            
            try {
                System.out.println(sqlValues);
                lector.insertaRegistro(sqlValues, renglonTratado + 1);
            } catch(SQLException sqle) {
                sqle.printStackTrace();
                System.exit(0);
            } catch(ExcelMalFormadoException emfe) {
                throw new XLSXException(emfe);
            }
                
            sqlValues = "";
            lastColumnNumber = -1;
        }
    }
    
    public void characters(char[] ch, int start, int length)
            throws SAXException {
        if (vIsOpen) {
            value.append(ch, start, length);
        }
    }

    int nameToColumn(String name) {
        int column = -1;
        for (int i = 0; i < name.length(); ++i) {
            int c = name.charAt(i);
            column = (column + 1) * 26 + c - 'A';
        }
        return column;
    }

    int validaMes(String mesStr) {
        int mes = -1;
        if(mesStr == null || mesStr.equals("") || mesStr.replaceAll("'", "").trim().equals("")) {
            return -1;
        }
        
        String[] mesSplit = mesStr.replaceAll("'", "").split("-");
        if(mesSplit.length != 2) {
            return -1;
        } else if(mesSplit[0].length() != 4 && mesSplit[1].length() != 2) {
            return -1;
        }
        
        try {
            Integer.parseInt(mesSplit[1]);
        } catch(Exception e) {
            return -1;
        }
        
        mes = Integer.parseInt(mesSplit[1]);
        return mes;
    }
    
}
